﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace UNF_Event_Posting
{
    public class epDataManager
    {
        private string conString = null;
        private SqlConnection conn;
        private SqlDataReader rdr;
        private SqlTransaction tran;
        

        public epDataManager(string conString)
        {
            this.conString = conString;
        }


        public SqlConnection getDataConnection(bool isTransaction)
        {
            try
            {
                conn = new SqlConnection(this.conString);
                conn.Open();
                if (isTransaction)
                {
                    tran = conn.BeginTransaction();
                }
            }
            catch (Exception ex)
            {
                throw new System.ArgumentException("Cannot open the connection: ", this.conString, ex);
            }
            return conn;
        }

        public Guid createEventPost(epDataObject ep)
        {
            Guid z;
            int y = 0;
            String sqlString = "INSERT INTO Event ";
            sqlString += createColValueString(ep) + ";";
            sqlString += "Select Scope_Identity();";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                y = int.Parse(cmd.ExecuteScalar().ToString());
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;

            }

            sqlString = "select id from Event where int_id=" + y.ToString() + ";";
            cmd = new SqlCommand(sqlString, getDataConnection(false));
            try
            {
                rdr = cmd.ExecuteReader();
                rdr.Read();
                z = (Guid)rdr["id"];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return z;
        }


        public List<Category> getAllCategoryItems()
        {
            SqlCommand cmd = new SqlCommand("Select * from Category", getDataConnection(false));
            return getDataObjects<Category>(cmd);
        }

        private String createColValueString(epDataObject ep)
        {
            String str = "";
            String fields = "(";
            String values = "values(";
            //SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            foreach (String x in ep.Keys)
            {
                object o = ep[x];
                if (o is string)
                {
                    o = ((string)o).Replace("'", "''");
                }
                fields += x + ",";
                values += "'" + o + "',";
                //values += ec[x] + ",\n";
            }
            char[] trimchar = { ',', '\n' };
            fields = fields.TrimEnd(trimchar) + ") ";
            values = values.TrimEnd(trimchar) + ")";
            str += fields + values;
            return str;
        }


        private List<EPType> getDataObjects<EPType>(SqlCommand cmd) where EPType : epDataObject, new()
        {
            cmd.CommandType = CommandType.Text;
            List<EPType> daObs = new List<EPType>();
            try
            {
                //execute the query
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    EPType daOb = new EPType();
                    //fieldnames.Clear();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        //this.fieldnames.Add(rdr.GetName(i).ToString());
                        daOb.Add(rdr.GetName(i).ToString(), rdr[rdr.GetName(i).ToString()]);

                    }

                    daObs.Add(daOb);
                }
            }
            catch (Exception ex)
            {
                String x = ex.Message;
            }
            finally
            {
                rdr.Close();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return daObs;
        }
        
    }

    
}